﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace DAO
{
    public class DAOBaoCao:DataProvider
    {
        public static DataTable BaoCaoNgay(DateTime ngay)
        {
            SqlConnection MoKetNoi = KetNoi();
            string chuoilenh = "select MonAn.TenMonAn,COUNT(ChiTietPhieuXuat.MaMonAn) as SL,SUM(ChiTietPhieuXuat.ThanhTien) as DoanhThu from PhieuXuat join ChiTietPhieuXuat on ChiTietPhieuXuat.maphieuxuat = PhieuXuat.maphieuxuat,MonAn where MonAn.MaMonAn = ChiTietPhieuXuat.MaMonAn and DATEPART(D,NgayXuat) = @day AND DATEPART(M,NgayXuat) = @month AND DATEPART(YYYY,NgayXuat) = @year group by ChiTietPhieuXuat.MaMonAn, MonAn.TenMonAn";
            DataTable BangKetQua = new DataTable();
            
            SqlDataAdapter da = new SqlDataAdapter(chuoilenh, MoKetNoi);
            SqlParameter values = new SqlParameter("@day", ngay.Day);
            da.SelectCommand.Parameters.Add(values);
            values = new SqlParameter("@month", ngay.Month);
            da.SelectCommand.Parameters.Add(values);
            values = new SqlParameter("@year", ngay.Year);
            da.SelectCommand.Parameters.Add(values);
            DataSet ds = new DataSet();
            da.Fill(ds);
            BangKetQua = ds.Tables[0];
            DongKetNoi();
            return BangKetQua;
        }
        public static DataTable BaoCaoMon(string mamon)
        {
            SqlConnection MoKetNoi = KetNoi();
            string chuoilenh = "select MonAn.TenMonAn, SUM(ChiTietPhieuXuat.SoLuong) as SL,SUM(ChiTietPhieuXuat.ThanhTien) as DoanhThu from MonAn, ChiTietPhieuXuat where MonAn.MaMonAn = @ma and MonAn.MaMonAn = ChiTietPhieuXuat.MaMonAn group by MonAn.TenMonAn";
            DataTable BangKetQua = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(chuoilenh, MoKetNoi);
            SqlParameter values = new SqlParameter("@ma", mamon);
            da.SelectCommand.Parameters.Add(values);
            DataSet ds = new DataSet();
            da.Fill(ds);
            BangKetQua = ds.Tables[0];
            DongKetNoi();
            return BangKetQua;
        }
    }
}
